use rusqlite::{Connection, Result as SqliteResult};
use chrono::{DateTime, Utc};
use crate::models::{LogEntry, AnalysisCase, LogFile};

pub struct DatabaseService {
    conn: Connection,
}

impl DatabaseService {
    pub fn new(db_path: &str) -> SqliteResult<Self> {
        let conn = Connection::open(db_path)?;
        let service = Self { conn };
        service.init_tables()?;
        Ok(service)
    }

    fn init_tables(&self) -> SqliteResult<()> {
        // 创建日志条目表
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS log_entries (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                level TEXT NOT NULL,
                source TEXT NOT NULL,
                message TEXT NOT NULL,
                file_path TEXT NOT NULL,
                line_number INTEGER,
                raw_content TEXT NOT NULL
            )",
            [],
        )?;

        // 创建分析用例表
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS analysis_cases (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT NOT NULL,
                category TEXT NOT NULL,
                patterns TEXT NOT NULL,
                ai_prompts TEXT NOT NULL,
                tags TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )",
            [],
        )?;

        // 创建分析结果表
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS analysis_results (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                case_id INTEGER NOT NULL,
                severity TEXT NOT NULL,
                title TEXT NOT NULL,
                summary TEXT NOT NULL,
                cause TEXT NOT NULL,
                impact TEXT NOT NULL,
                suggestions TEXT NOT NULL,
                created_at TEXT NOT NULL,
                FOREIGN KEY (case_id) REFERENCES analysis_cases (id)
            )",
            [],
        )?;

        // 创建日志文件表
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS log_files (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                path TEXT NOT NULL UNIQUE,
                size INTEGER NOT NULL,
                imported_at TEXT NOT NULL,
                entry_count INTEGER NOT NULL
            )",
            [],
        )?;

        Ok(())
    }

    // 日志条目相关方法
    pub fn insert_log_entries(&mut self, entries: &[LogEntry]) -> SqliteResult<()> {
        let tx = self.conn.transaction()?;
        
        for entry in entries {
            tx.execute(
                "INSERT INTO log_entries (timestamp, level, source, message, file_path, line_number, raw_content)
                 VALUES (?, ?, ?, ?, ?, ?, ?)",
                [
                    &entry.timestamp.to_rfc3339(),
                    &entry.level,
                    &entry.source,
                    &entry.message,
                    &entry.file_path,
                    &entry.line_number.map(|n| n.to_string()).unwrap_or_default(),
                    &entry.raw_content,
                ],
            )?;
        }
        
        tx.commit()?;
        Ok(())
    }

    pub fn get_log_entries(&self, limit: Option<i64>, offset: Option<i64>) -> SqliteResult<Vec<LogEntry>> {
        let limit = limit.unwrap_or(100);
        let offset = offset.unwrap_or(0);
        
        let mut stmt = self.conn.prepare(
            "SELECT id, timestamp, level, source, message, file_path, line_number, raw_content
             FROM log_entries
             ORDER BY timestamp DESC
             LIMIT ? OFFSET ?"
        )?;
        
        let entries = stmt.query_map([limit, offset], |row| {
            Ok(LogEntry {
                id: Some(row.get(0)?),
                timestamp: DateTime::parse_from_rfc3339(&row.get::<_, String>(1)?)
                    .unwrap_or_else(|_| Utc::now().into())
                    .with_timezone(&Utc),
                level: row.get(2)?,
                source: row.get(3)?,
                message: row.get(4)?,
                file_path: row.get(5)?,
                line_number: row.get(6)?,
                raw_content: row.get(7)?,
            })
        })?
        .collect::<SqliteResult<Vec<_>>>()?;
        
        Ok(entries)
    }

    // 分析用例相关方法
    pub fn insert_analysis_case(&self, case: &AnalysisCase) -> SqliteResult<i64> {
        let id = self.conn.execute(
            "INSERT INTO analysis_cases (name, description, category, patterns, ai_prompts, tags, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            [
                &case.name,
                &case.description,
                &case.category,
                &serde_json::to_string(&case.patterns).unwrap_or_default(),
                &serde_json::to_string(&case.ai_prompts).unwrap_or_default(),
                &serde_json::to_string(&case.tags).unwrap_or_default(),
                &case.created_at.to_rfc3339(),
                &case.updated_at.to_rfc3339(),
            ],
        )?;
        
        Ok(id as i64)
    }

    pub fn get_analysis_cases(&self) -> SqliteResult<Vec<AnalysisCase>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, name, description, category, patterns, ai_prompts, tags, created_at, updated_at
             FROM analysis_cases
             ORDER BY created_at DESC"
        )?;
        
        let cases = stmt.query_map([], |row| {
            Ok(AnalysisCase {
                id: Some(row.get(0)?),
                name: row.get(1)?,
                description: row.get(2)?,
                category: row.get(3)?,
                patterns: serde_json::from_str(&row.get::<_, String>(4)?).unwrap_or_default(),
                ai_prompts: serde_json::from_str(&row.get::<_, String>(5)?).unwrap_or_default(),
                tags: serde_json::from_str(&row.get::<_, String>(6)?).unwrap_or_default(),
                created_at: DateTime::parse_from_rfc3339(&row.get::<_, String>(7)?)
                    .unwrap_or_else(|_| Utc::now().into())
                    .with_timezone(&Utc),
                updated_at: DateTime::parse_from_rfc3339(&row.get::<_, String>(8)?)
                    .unwrap_or_else(|_| Utc::now().into())
                    .with_timezone(&Utc),
            })
        })?
        .collect::<SqliteResult<Vec<_>>>()?;
        
        Ok(cases)
    }

    // 日志文件相关方法
    pub fn insert_log_file(&self, file: &LogFile) -> SqliteResult<i64> {
        let id = self.conn.execute(
            "INSERT OR REPLACE INTO log_files (name, path, size, imported_at, entry_count)
             VALUES (?, ?, ?, ?, ?)",
            [
                &file.name,
                &file.path,
                &file.size.to_string(),
                &file.imported_at.to_rfc3339(),
                &file.entry_count.to_string(),
            ],
        )?;
        
        Ok(id as i64)
    }

    pub fn get_log_files(&self) -> SqliteResult<Vec<LogFile>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, name, path, size, imported_at, entry_count
             FROM log_files
             ORDER BY imported_at DESC"
        )?;
        
        let files = stmt.query_map([], |row| {
            Ok(LogFile {
                id: Some(row.get(0)?),
                name: row.get(1)?,
                path: row.get(2)?,
                size: row.get(3)?,
                imported_at: DateTime::parse_from_rfc3339(&row.get::<_, String>(4)?)
                    .unwrap_or_else(|_| Utc::now().into())
                    .with_timezone(&Utc),
                entry_count: row.get(5)?,
            })
        })?
        .collect::<SqliteResult<Vec<_>>>()?;
        
        Ok(files)
    }
} 